package gateway;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import gateway.exception.CarpoolDBException;
import model.Carpool;
import session.CookieManager;
import session.SessionManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: Tom
 * Date: 19-2-14
 * Time: 19:41
 */
public class CarpoolDB extends DatabaseGateway {

    public void joinCarpoolGroup(String email, String group_id) throws CarpoolDBException, SQLException {
        Connection connection = openConnection();

        int result;
        try {
            PreparedStatement statement = connection.prepareStatement(
                    "insert into CARPOOL_USER  (CARPOOL_ID, EMAIL) values (?, ?);");
            statement.setInt(1, Integer.parseInt(group_id));
            statement.setString(2, email);
            result = statement.executeUpdate();

            connection.close();
        }  catch (MySQLIntegrityConstraintViolationException e){
            throw new CarpoolDBException("Carpool group " + group_id + " doesn't exist or you have already joined it.");
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }

        if (result != 1){
            throw new CarpoolDBException("An error has occurred, you didn't join Carpool group "+group_id+".");
        }
        CookieManager.setGroup_ids(getJoinedCarpoolGroups(SessionManager.getEmail()));
    }

    public List<Carpool> getJoinedCarpoolGroups(String email) throws CarpoolDBException, SQLException{
        Connection connection = openConnection();
        ResultSet result;
        try {
            PreparedStatement statement = connection.prepareStatement(
                    "select CARPOOL_ID, NAME from CARPOOL_USER join CARPOOL on CARPOOL_ID = ID where EMAIL=?;");
            statement.setString(1, email);
            result = statement.executeQuery();

            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }

        ArrayList<Carpool> carpool_id_list = new ArrayList<>();
        while(result.next()){
            Carpool c = new Carpool(result.getString("CARPOOL_ID"), result.getString("NAME"));
            carpool_id_list.add(c);
        }

        return carpool_id_list;
    }


    public void createCarpoolGroup(String carpool_name, String email) throws CarpoolDBException, SQLException {
        Connection connection = openConnection();

        try {
            System.out.println("carpool_name = " + carpool_name);
            System.out.println("email = " + email);
            PreparedStatement statement = connection.prepareStatement(
                    "call sp_CreateGroup(?, ?);");
            statement.setString(1, carpool_name);
            statement.setString(2, email);
            statement.executeQuery();

            connection.close();
            CookieManager.setGroup_ids(getJoinedCarpoolGroups(SessionManager.getEmail()));
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }



}
